﻿create PROCEDURE Sync.SP_CommitteeBankAccountExpenses_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CommitteeBankAccountExpenses_Temp TABLE 
		(
			CommitteeBankAccountExpensesID UniqueIdentifier,
			[CommitteeBankAccountID] uniqueidentifier,
				[WithdrawDate] datetime,
				[WithdrawAmount] float,
				[WithdrawCheckNumber] nvarchar(50),
				[Notes] nvarchar(MAX),
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CommitteeBankAccountExpenses_Temp 
	   SELECT	Tbl.Col.value('CommitteeBankAccountExpensesID[1]','uniqueidentifier') CommitteeBankAccountExpensesID,
                Tbl.Col.value('CommitteeBankAccountID[1]', 'uniqueidentifier') [CommitteeBankAccountID],
Tbl.Col.value('WithdrawDate[1]', 'datetime') [WithdrawDate],
Tbl.Col.value('WithdrawAmount[1]', 'float') [WithdrawAmount],
Tbl.Col.value('WithdrawCheckNumber[1]', 'nvarchar(50)') [WithdrawCheckNumber],
Tbl.Col.value('Notes[1]', 'nvarchar(MAX)') [Notes],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CommitteeBankAccountExpenses') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CommitteeBankAccountExpenses] ([CommitteeBankAccountExpensesID],[CommitteeBankAccountID],[WithdrawDate],[WithdrawAmount],[WithdrawCheckNumber],[Notes])
	SELECT t.[CommitteeBankAccountExpensesID],t.[CommitteeBankAccountID],t.[WithdrawDate],t.[WithdrawAmount],t.[WithdrawCheckNumber],t.[Notes]
	FROM  @CommitteeBankAccountExpenses_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CommitteeBankAccountExpenses_Tracking s
        WHERE t.CommitteeBankAccountExpensesID = s.CommitteeBankAccountExpensesID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CommitteeBankAccountExpenses]
	SET [CommitteeBankAccountExpenses].[CommitteeBankAccountID] = t.[CommitteeBankAccountID],[CommitteeBankAccountExpenses].[WithdrawDate] = t.[WithdrawDate],[CommitteeBankAccountExpenses].[WithdrawAmount] = t.[WithdrawAmount],[CommitteeBankAccountExpenses].[WithdrawCheckNumber] = t.[WithdrawCheckNumber],[CommitteeBankAccountExpenses].[Notes] = t.[Notes] 
	FROM [dbo].[CommitteeBankAccountExpenses] s JOIN @CommitteeBankAccountExpenses_Temp t 
    ON t.CommitteeBankAccountExpensesID = s.CommitteeBankAccountExpensesID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CommitteeBankAccountExpenses_Tracking r
				  WHERE t.CommitteeBankAccountExpensesID = r.CommitteeBankAccountExpensesID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CommitteeBankAccountExpenses]
    FROM @CommitteeBankAccountExpenses_Temp t JOIN [dbo].[CommitteeBankAccountExpenses] s
    ON t.CommitteeBankAccountExpensesID = s.CommitteeBankAccountExpensesID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END











